"""
Case Type   : 功能测试
Case Name   : MOT不支持raw数据类型，类型强转后可插入数据
Create At   : 2022/05/17
Owner       : @kyeleze
Description :
    1.设置enable_incremental_checkpoint参数为off,并重启数据库
    2.创建自定义转化函数
    3.创建内存表，字段类型含integer
    4.创建自定义转化关系
    5.隐含调用raw类型转化为整型，插入数据
    6.清理环境，恢复配置
Expect      :
    1.设置成功，重启数据库成功
    2.自定义转化函数创建成功
    3.创建内存表成功
    4.自定义转化关系创建成功
    5.类型转化成功，数据插入成功
    6.清理环境及恢复配置成功
History     :
"""

import unittest
import os
import time
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger
logger = Logger()


class TestCreateCast0076(unittest.TestCase):
    def setUp(self):
        logger.info(f'--------{os.path.basename(__file__)} start--------')
        self.sh_primysh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.function_name = 'f_create_cast_0076'
        self.mot_table = 't_create_cast_0076'
        self.config_item = "enable_incremental_checkpoint"
        self.mot_engine = 'enable_mot_server'
        text = '--------开启mot特性  ' \
               'expect:修改成功，重启数据库成功--------'
        logger.info(text)
        self.engine_res = self.com.show_param(f'{self.mot_engine}')
        self.sh_primysh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'{self.mot_engine} = on')
        self.sh_primysh.restart_db_cluster()
        status = self.sh_primysh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)

        text = '--------step1:修改enable_incremental_checkpoint为off  ' \
               'expect:修改成功，重启数据库成功--------'
        logger.info(text)
        check_res = self.sh_primysh.execut_db_sql(f'show {self.config_item}')
        logger.info(check_res)
        self.assertTrue(len(check_res.splitlines()) == 4, '执行失败'+text)
        self.init_config = check_res.splitlines()[2].strip()
        self.assertTrue("on" in self.init_config or
                        "off" in self.init_config, '执行失败:' + text)
        if 'on' in self.init_config:
            self.sh_primysh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'{self.config_item} = off')
            self.sh_primysh.restart_db_cluster()
            time.sleep(3)
            status = self.sh_primysh.get_db_cluster_status()
            logger.info(status)
            self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)

    def test_create_cast_0076(self):
        text = '--------step2:创建自定义转化函数   expect:函数创建成功--------'
        logger.info(text)
        self.func_cmd = f'''
        create or replace function {self.function_name}(in raw)
               returns integer
               as
               \$\$
               declare
               begin
               return 10;
               end;
               \$\$
               language plpgsql;                       
                      '''
        logger.info(self.func_cmd)
        function_msg = self.sh_primysh.execut_db_sql(self.func_cmd)
        logger.info(function_msg)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                      function_msg, '执行失败' + text)
        text = '--------step3:创建内存表，字段类型含integer ' \
               'expect：创建成功--------'
        logger.info(text)
        self.sql_cmd = f'''
        drop table if exists {self.mot_table} cascade;
        drop foreign table if exists {self.mot_table} cascade;
        create foreign table {self.mot_table}
        (i integer, b time with time zone);'''
        logger.info(self.sql_cmd)
        sql_msg = self.sh_primysh.execut_db_sql(self.sql_cmd)
        logger.info(sql_msg)
        self.assertIn(self.constant.CREATE_FOREIGN_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        text = '--------step4: 创建自定义转换关系    expect: 创建成功--------'
        logger.info(text)
        self.cast_cmd = f'''
        drop cast if exists (raw as integer) cascade;
        drop cast if exists (text as time with time zone ) cascade;
        create cast(raw as integer) with function {self.function_name}
        (raw) as implicit;
        create cast(text as time with time zone) with inout as implicit;
        '''
        logger.info(self.cast_cmd)
        cast_msg = self.sh_primysh.execut_db_sql(self.cast_cmd)
        logger.info(cast_msg)
        self.assertIn('CREATE CAST', cast_msg, '执行失败' + text)
        text = '--------step5:隐含调用raw强转为整型的数据类型插入内存表中   ' \
               'expect: 插入数据成功--------'
        logger.info(text)
        self.insert_cmd = f'''
        insert into {self.mot_table} values(hextoraw('A'), '205609');
        select * from {self.mot_table};'''
        logger.info(self.insert_cmd)
        insert_msg = self.sh_primysh.execut_db_sql(self.insert_cmd)
        logger.info(insert_msg)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      insert_msg, '执行失败' + text)

    def tearDown(self):
        text = '--------step6:后置处理,清理环境   expect:清理成功--------'
        logger.info(text)
        self.rm_cmd = f'''
        drop cast if exists (raw as integer) cascade;
        drop function if exists {self.function_name}(raw) cascade;
        drop foreign table if exists {self.mot_table} cascade;
        '''
        logger.info(self.rm_cmd)
        rm_msg = self.sh_primysh.execut_db_sql(self.rm_cmd)
        logger.info(rm_msg)
        logger.info('--------恢复enable_incremental_checkpoint默认配置--------')
        self.assertTrue("on" in self.init_config or "off" in self.init_config)
        if "on" in self.init_config:
            self.mod_msg = self.sh_primysh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f'{self.config_item} = on')
            logger.info('--------重启数据库--------')
            restart_msg = self.sh_primysh.restart_db_cluster()
            time.sleep(3)
            logger.info(restart_msg)
            status = self.sh_primysh.get_db_cluster_status()
            logger.info(status)
            self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)
        else:
            logger.info('--------参数enable_incremental_checkpoint未做修改,'
                        '不用恢复--------')

        self.assertIn(self.constant.DROP_FOREIGN_SUCCESS_MSG, 
                      rm_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, 
                      rm_msg, '执行失败' + text)
        self.assertIn('DROP CAST', rm_msg, '执行失败' + text)
        text = '--------还原mot特性  ' \
               'expect:修改成功，重启数据库成功--------'
        logger.info(text)
        self.sh_primysh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f'{self.mot_engine} = {self.engine_res}')
        status = self.sh_primysh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status,
                            '执行失败:' + text)
        logger.info(f'--------{os.path.basename(__file__)} end--------')
